﻿CREATE PROCEDURE [util].[GetFailedJobs]
@ScopeInHrs TINYINT=24
AS
BEGIN
	SELECT
		MAX(util.GetDateFromInt(h.[run_date],h.[run_time])) AS [Last Run Time]
		,j.[name] AS [Job Name]
		,h.[step_name] AS [Step Name]
		,COUNT(*) AS [Occurances]
		,MAX(h.[run_duration]) AS [Max Duration]
		,MAX(h.[sql_severity]) AS [Max Severity]
	FROM [msdb].dbo.sysjobhistory AS h
	INNER JOIN [msdb].dbo.sysjobs AS j
		ON (j.[job_id] = h.[job_id])
	WHERE  ([run_status] = 0)
		AND([step_id] > 0)
		AND(DATEDIFF(hh,util.GetDateFromInt(h.[run_date],h.[run_time]),GETDATE()) <= @ScopeInHrs)
	GROUP BY
		j.[name]
		,h.[step_name]
	ORDER BY
		[Job Name]
		,[Last Run Time] DESC
END